-- 客户异常监控汇总表
DROP TABLE IF EXISTS jms_dm.dm_project_customer_exception_monitor_total_dt;
CREATE EXTERNAL TABLE IF NOT EXISTS jms_dm.dm_project_customer_exception_monitor_total_dt(
input_date                      String           COMMENT '查询日期(运单的录入日期)',
project_customer_id             String           COMMENT '项目客户id',
project_customer_name           String           COMMENT '项目客户名称',
subordinate_agent_code          String           COMMENT '代理区编码',
subordinate_agent_name          String           COMMENT '代理区名称',
franchisee_code                 String           COMMENT '加盟商编码',
franchisee_name                 String           COMMENT '加盟商名称',
network_code                    String           COMMENT '网点编码',
network_name                    String           COMMENT '网点名称',
-- customer_code                   String           COMMENT '客户编码',
-- customer_name                   String           COMMENT '客户名称',
total_input                     int              COMMENT '发货量',
total_complaint                 int              COMMENT '客诉量',
rate_complaint                  decimal(18,5)    COMMENT '客诉率',
total_intercept                 int              COMMENT '拦截退转登记量',
total_print                     int              COMMENT '拦截退转面单打印量',
total_send                      int              COMMENT '拦截后发件量',
total_sign                      int              COMMENT '拦截后签收量',
total_not_sign                  int              COMMENT '拦截后未签收量',
total_not_intercept             int              COMMENT '未拦截量',
rate_intercept                  decimal(18,5)    COMMENT '拦截率',
total_loss                      int              COMMENT '遗失量',
total_broken                    int              COMMENT '破损量'
)
COMMENT '客户异常监控汇总表'
PARTITIONED BY (dt string COMMENT '日期(yyyy-MM-dd)')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_project_customer_exception_monitor_total_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);